#!/usr/bin/env python
# -*- encoding: utf-8 -*-

import pandas as pd
import datetime,time
from xpy3lib.XRetryableQuery import XRetryableQuery
from xpy3lib.XRetryableSave import XRetryableSave
from sicost.AbstractDPJob import AbstractDPJob
from xpy3lib.utils.XDateTimeUtils import XDateTimeUtils

class BZBZFYJob(AbstractDPJob):
    """


    """
    month = None


    def __init__(self,
                 p_config=None,
                 p_db_conn_mpp=None,
                 p_db_conn_dbprod7=None,
                 p_db_conn_rds=None,
                 p_account=None,
                 p_month=None):

        super(BZBZFYJob, self).__init__(p_config=p_config,
                                         p_db_conn_mpp=p_db_conn_mpp,
                                         p_db_conn_rds=p_db_conn_rds,
                                         p_db_conn_dbprod7=p_db_conn_dbprod7,
                                         p_unit=None,
                                         p_account=p_account,
                                         p_cost_center=None,
                                         p_account_period_start=None,
                                         p_account_period_end=None)

        self.month = p_month


        pass

    def do_execute(self):
        """
        """
        self.logger.info('BZBZFYJob.do_execute')

        #   --判断当月是否有数据
        #V_YEAR = TO_CHAR(TO_DATE(P_MONTH,'yyyyMM') - 1 YEAR ,'yyyy')
        month = "%s01200000" % (self.month)

        month = datetime.datetime.strptime(month, '%Y%m%tmp_dict%H%M%S')


        p_month_1month = XDateTimeUtils.advance(month, p_months=-1)
        v_month = datetime.datetime(year=p_month_1month.year, month=p_month_1month.month, day=p_month_1month.day).strftime('%Y%m')
        p_month_1year = XDateTimeUtils.advance(month, p_months=-12)
        v_year = datetime.datetime(year=p_month_1year.year, month=p_month_1year.month, day=p_month_1year.day).strftime('%Y')
        #V_MONTH = TO_CHAR(TO_DATE(P_MONTH,'yyyyMM') + 1 MONTH ,'yyyyMM')
        sql = " SELECT COUNT(*) as V_COUNT FROM BGTARAS1.T_ADS_FACT_SICB_BZBZFY " \
              " WHERE 1=1" \
              " AND ACCT = '%s' " \
              " AND MONTH = '%s' " % (
                  self.account, v_month)

        self.logger.info(sql)

        df1 = XRetryableQuery(p_db_conn=self.db_conn_rds, p_sql=sql, p_max_times=5).redo()
        success = df1.empty is False
        if success is False:
            return

        v = df1.iloc[0,0] if success else None
        if v <= 0:
            sql = " SELECT " \
                  " LEFT('%s',4) AS YEAR, " \
                  " A.ACCOUNT, " \
                  " A.QS_NAME, " \
                  " A.TYPE, " \
                  " CAST (A.AMOUNT_ACT AS DECIMAL (22, 2)) / B.EARN_N AS AMOUT_ACT, " \
                  " '%s' AS YEAR_MON " \
                  " FROM (SELECT ACCOUNT, " \
                  " YEAR||MONTH AS YEAR_MON," \
                  " CASE " \
                  " WHEN COST_CENTER IN ('MIDN', " \
                  " 'MIDM','MIDY','MIDZ') " \
                  " THEN '无取向硅钢' " \
                  " WHEN COST_CENTER IN ('MICX', " \
                  " 'MIHY','MIHZ','MICZ') " \
                  " THEN '取向硅钢' " \
                  " END AS QS_NAME, " \
                  " CASE WHEN LEFT (WCE, 1) = '3' THEN '修理费' " \
                  " WHEN LEFT (WCE, 1) = '6' THEN '资材费' " \
                  " WHEN LEFT (WCE, 1) = '7' THEN '人工费' " \
                  " WHEN LEFT (WCE, 1) = '8' THEN '厂务费' " \
                  " END AS TYPE, " \
                  " SUM (ACT_AMOUNT_AC_1 + RCCL_VRNC_AMOUNT) AS AMOUNT_ACT " \
                  " FROM BGRAGGCB.TACACM7 " \
                  " WHERE     ACCOUNT = '%s' " \
                  " AND YEAR||MONTH = '%s' " \
                  " AND LEFT (WCE, 1) IN ('3', " \
                  " '6','7','8')  " \
                  "  AND COST_CENTER IN ('MIDN', " \
                  " 'MIDM','MIDY','MIDZ','MICX','MIHY','MIHZ','MICZ') " \
                  " GROUP BY ACCOUNT,YEAR||MONTH, " \
                  " CASE " \
                  " WHEN COST_CENTER IN ('MIDN', " \
                  " 'MIDM','MIDY','MIDZ')  " \
                  " THEN '无取向硅钢' " \
                  " WHEN COST_CENTER IN ('MICX', " \
                  " 'MIHY','MIHZ','MICZ') " \
                  " THEN '取向硅钢' END, " \
                  " CASE WHEN LEFT (WCE, 1) = '3' THEN '修理费'" \
                  " WHEN LEFT (WCE, 1) = '6' THEN '资材费'" \
                  " WHEN LEFT (WCE, 1) = '7' THEN '人工费'" \
                  " WHEN LEFT (WCE, 1) = '8' THEN '厂务费'" \
                  " END) A " \
                  " LEFT JOIN (SELECT ACCOUNT,YEAR||MONTH AS YEAR_MON, " \
                  " CASE WHEN COST_CENTER IN ('MIDN', " \
                  " 'MIDM','MIDY','MIDZ') " \
                  " THEN '无取向硅钢' " \
                  " WHEN COST_CENTER IN ('MICX'," \
                  " 'MIHY','MIHZ','MICZ') " \
                  " THEN '取向硅钢' END AS QS_NAME, " \
                  " SUM (ACT_DET_VALUE_1) AS EARN_N " \
                  " FROM BGRAGGCB.TACACM8 " \
                  " WHERE ACCOUNT = '%s' " \
                  " AND YEAR||MONTH = '%s' " \
                  " GROUP BY ACCOUNT,YEAR||MONTH, " \
                  " CASE WHEN COST_CENTER IN ('MIDN'," \
                  " 'MIDM','MIDY','MIDZ') " \
                  " THEN '无取向硅钢' " \
                  " WHEN COST_CENTER IN ('MICX'," \
                  " 'MIHY','MIHZ','MICZ') " \
                  " THEN '取向硅钢' END) B " \
                  " ON     A.ACCOUNT = B.ACCOUNT" \
                  " AND A.YEAR_MON = B.YEAR_MON " \
                  " AND A.QS_NAME = B.QS_NAME" \
                  " WHERE B.EARN_N <> 0" % (v_month, v_month, self.account, self.month, self.account, self.month)
            self.logger.info(sql)

            df0 = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
            success = df0.empty is False
            if success is False:
                return
            df0.columns = df0.columns.str.upper()

            df0.rename(columns={'ACCOUNT': 'ACCT'}, inplace=True)
            df0.rename(columns={'QS_NAME': 'PROD_CAT'}, inplace=True)
            df0.rename(columns={'TYPE': 'FEE_TYPE'}, inplace=True)
            df0.rename(columns={'AMOUT_ACT': 'UNIT_COST'}, inplace=True)
            df0.rename(columns={'YEAR_MON': 'MONTH'}, inplace=True)

            sql = " DELETE FROM BGTARAS1.T_ADS_FACT_SICB_BZBZFY WHERE ACCT = '%s' AND YEAR = LEFT('%s',4) AND NVL(MONTH,'') = '' " % (
            self.account, self.month)

            self.logger.info(sql)
            self.db_conn_rds.execute(sql)

            XRetryableSave(p_db_conn=self.db_conn_rds, p_table_name='T_ADS_FACT_SICB_BZBZFY', p_schema='BGTARAS1',
                           p_dataframe=df0,
                           p_max_times=5).redo()


        #当年
        sql = " DELETE FROM BGTARAS1.T_ADS_FACT_SICB_BZBZFY WHERE ACCT = '%s' AND YEAR = LEFT('%s',4) AND NVL(MONTH,'') = ''"%(self.account,self.month)

        self.logger.info(sql)
        self.db_conn_rds.execute(sql)

        sql = " SELECT " \
              " LEFT('%s',4) AS YEAR, " \
              " A.ACCOUNT, " \
              " A.QS_NAME, " \
              " A.TYPE, " \
              " CAST (A.AMOUNT_ACT AS DECIMAL (22, 2)) / B.EARN_N AS AMOUT_ACT " \
              " FROM (SELECT ACCOUNT, " \
              " YEAR||MONTH AS YEAR_MON," \
              " CASE " \
              " WHEN COST_CENTER IN ('MIDN', " \
              " 'MIDM','MIDY','MIDZ') " \
              " THEN '无取向硅钢' " \
              " WHEN COST_CENTER IN ('MICX', " \
              " 'MIHY','MIHZ','MICZ') " \
              " THEN '取向硅钢' " \
              " END AS QS_NAME, " \
              " CASE WHEN LEFT (WCE, 1) = '3' THEN '修理费' " \
              " WHEN LEFT (WCE, 1) = '6' THEN '资材费' " \
              " WHEN LEFT (WCE, 1) = '7' THEN '人工费' " \
              " WHEN LEFT (WCE, 1) = '8' THEN '厂务费' " \
              " END AS TYPE, " \
              " SUM (ACT_AMOUNT_AC_1 + RCCL_VRNC_AMOUNT) AS AMOUNT_ACT " \
              " FROM BGRAGGCB.TACACM7 " \
              " WHERE     ACCOUNT = '%s' " \
              " AND YEAR = LEFT('%s',4) " \
              " AND LEFT (WCE, 1) IN ('3', " \
              " '6','7','8')  " \
              "  AND COST_CENTER IN ('MIDN', " \
              " 'MIDM','MIDY','MIDZ','MICX','MIHY','MIHZ','MICZ') " \
              " GROUP BY ACCOUNT, YEAR||MONTH, " \
              " CASE " \
              " WHEN COST_CENTER IN ('MIDN', " \
              " 'MIDM','MIDY','MIDZ')  " \
              " THEN '无取向硅钢' " \
              " WHEN COST_CENTER IN ('MICX', " \
              " 'MIHY','MIHZ','MICZ') " \
              " THEN '取向硅钢' END, " \
              " CASE WHEN LEFT (WCE, 1) = '3' THEN '修理费'" \
              " WHEN LEFT (WCE, 1) = '6' THEN '资材费'" \
              " WHEN LEFT (WCE, 1) = '7' THEN '人工费'" \
              " WHEN LEFT (WCE, 1) = '8' THEN '厂务费'" \
              " END) A " \
              " LEFT JOIN (SELECT ACCOUNT,YEAR||MONTH AS YEAR_MON, " \
              " CASE WHEN COST_CENTER IN ('MIDN', " \
              " 'MIDM','MIDY','MIDZ') " \
              " THEN '无取向硅钢' " \
              " WHEN COST_CENTER IN ('MICX'," \
              " 'MIHY','MIHZ','MICZ') " \
              " THEN '取向硅钢' END AS QS_NAME, " \
              " SUM (ACT_DET_VALUE_1) AS EARN_N " \
              " FROM BGRAGGCB.TACACM8 " \
              " WHERE ACCOUNT = '%s' " \
              " AND YEAR = LEFT('%s',4) " \
              " GROUP BY ACCOUNT, YEAR||MONTH, " \
              " CASE WHEN COST_CENTER IN ('MIDN'," \
              " 'MIDM','MIDY','MIDZ') " \
              " THEN '无取向硅钢' " \
              " WHEN COST_CENTER IN ('MICX'," \
              " 'MIHY','MIHZ','MICZ') " \
              " THEN '取向硅钢' END) B " \
              " ON     A.ACCOUNT = B.ACCOUNT" \
              " AND A.QS_NAME = B.QS_NAME" \
              " WHERE B.EARN_N <> 0"%(v_month,self.account,self.month,self.account,self.month)
        self.logger.info(sql)
        df0 = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
        success = df0.empty is False
        if success is False:
            return
        df0.columns = df0.columns.str.upper()
        df0.rename(columns={'ACCOUNT': 'ACCT'}, inplace=True)
        df0.rename(columns={'QS_NAME': 'PROD_CAT'}, inplace=True)
        df0.rename(columns={'TYPE': 'FEE_TYPE'}, inplace=True)
        df0.rename(columns={'AMOUT_ACT': 'UNIT_COST'}, inplace=True)
        df0.rename(columns={'YEAR_MON': 'MONTH'}, inplace=True)

        XRetryableSave(p_db_conn=self.db_conn_rds, p_table_name='T_ADS_FACT_SICB_BZBZFY', p_schema='BGTARAS1',
                       p_dataframe=df0,
                       p_max_times=5).redo()
        #年初将上年平均插入
        if self.month[-2:] == '01':
            sql = " DELETE FROM BGTARAS1.T_ADS_FACT_SICB_BZBZFY WHERE ACCT = '%s' AND YEAR = '%s' AND NVL(MONTH,'') = ''" % (
            self.account, v_year)

            self.logger.info(sql)
            self.db_conn_rds.execute(sql)
            sql = " SELECT " \
                  " '%s' AS YEAR, " \
                  " A.ACCOUNT, " \
                  " A.QS_NAME, " \
                  " A.TYPE, " \
                  " CAST (A.AMOUNT_ACT AS DECIMAL (22, 2)) / B.EARN_N AS AMOUT_ACT " \
                  " FROM (SELECT ACCOUNT, " \
                  " YEAR||MONTH AS YEAR_MON," \
                  " CASE " \
                  " WHEN COST_CENTER IN ('MIDN', " \
                  " 'MIDM','MIDY','MIDZ') " \
                  " THEN '无取向硅钢' " \
                  " WHEN COST_CENTER IN ('MICX', " \
                  " 'MIHY','MIHZ','MICZ') " \
                  " THEN '取向硅钢' " \
                  " END AS QS_NAME, " \
                  " CASE WHEN LEFT (WCE, 1) = '3' THEN '修理费' " \
                  " WHEN LEFT (WCE, 1) = '6' THEN '资材费' " \
                  " WHEN LEFT (WCE, 1) = '7' THEN '人工费' " \
                  " WHEN LEFT (WCE, 1) = '8' THEN '厂务费' " \
                  " END AS TYPE, " \
                  " SUM (ACT_AMOUNT_AC_1 + RCCL_VRNC_AMOUNT) AS AMOUNT_ACT " \
                  " FROM BGRAGGCB.TACACM7 " \
                  " WHERE     ACCOUNT = '%s' " \
                  " AND YEAR = '%s' " \
                  " AND LEFT (WCE, 1) IN ('3', " \
                  " '6','7','8')  " \
                  "  AND COST_CENTER IN ('MIDN', " \
                  " 'MIDM','MIDY','MIDZ','MICX','MIHY','MIHZ','MICZ') " \
                  " GROUP BY ACCOUNT, YEAR||MONTH, " \
                  " CASE " \
                  " WHEN COST_CENTER IN ('MIDN', " \
                  " 'MIDM','MIDY','MIDZ')  " \
                  " THEN '无取向硅钢' " \
                  " WHEN COST_CENTER IN ('MICX', " \
                  " 'MIHY','MIHZ','MICZ') " \
                  " THEN '取向硅钢' END, " \
                  " CASE WHEN LEFT (WCE, 1) = '3' THEN '修理费'" \
                  " WHEN LEFT (WCE, 1) = '6' THEN '资材费'" \
                  " WHEN LEFT (WCE, 1) = '7' THEN '人工费'" \
                  " WHEN LEFT (WCE, 1) = '8' THEN '厂务费'" \
                  " END) A " \
                  " LEFT JOIN (SELECT ACCOUNT,YEAR||MONTH AS YEAR_MON, " \
                  " CASE WHEN COST_CENTER IN ('MIDN', " \
                  " 'MIDM','MIDY','MIDZ') " \
                  " THEN '无取向硅钢' " \
                  " WHEN COST_CENTER IN ('MICX'," \
                  " 'MIHY','MIHZ','MICZ') " \
                  " THEN '取向硅钢' END AS QS_NAME, " \
                  " SUM (ACT_DET_VALUE_1) AS EARN_N " \
                  " FROM BGRAGGCB.TACACM8 " \
                  " WHERE ACCOUNT = '%s' " \
                  " AND YEAR = '%s' " \
                  " GROUP BY ACCOUNT,YEAR||MONTH, " \
                  " CASE WHEN COST_CENTER IN ('MIDN'," \
                  " 'MIDM','MIDY','MIDZ') " \
                  " THEN '无取向硅钢' " \
                  " WHEN COST_CENTER IN ('MICX'," \
                  " 'MIHY','MIHZ','MICZ') " \
                  " THEN '取向硅钢' END) B " \
                  " ON     A.ACCOUNT = B.ACCOUNT" \
                  " AND A.QS_NAME = B.QS_NAME" \
                  " WHERE B.EARN_N <> 0" % (v_year, self.account, v_year, self.account, v_year)
            self.logger.info(sql)
            df0 = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
            success = df0.empty is False
            if success is False:
                return
            df0.columns = df0.columns.str.upper()

            df0.rename(columns={'ACCOUNT': 'ACCT'}, inplace=True)
            df0.rename(columns={'QS_NAME': 'PROD_CAT'}, inplace=True)
            df0.rename(columns={'TYPE': 'FEE_TYPE'}, inplace=True)
            df0.rename(columns={'AMOUT_ACT': 'UNIT_COST'}, inplace=True)
            df0.rename(columns={'YEAR_MON': 'MONTH'}, inplace=True)
            XRetryableSave(p_db_conn=self.db_conn_rds, p_table_name='T_ADS_FACT_SICB_BZBZFY', p_schema='BGTARAS1',
                           p_dataframe=df0,
                           p_max_times=5).redo()






        super(BZBZFYJob, self).do_execute()
